package com.orange.base.core.util;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Component;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.util.StringUtils;

import com.github.pagehelper.PageRowBounds;
import com.orange.base.core.bean.TableMetaData;
import com.orange.base.core.dao.DaoService;

@Component
public final class DbUtil {

    public static final DateTimeFormatter DATE_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd");

    public static final DateTimeFormatter DATETIME_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

    public static final DateTimeFormatter LONG_DATETIME_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss SSS");

    public static DaoService daoSevice;

    public static DaoService getDaoSevice() {
        return daoSevice;
    }

    @Autowired
    public void setDaoService(DaoService daoSevice) {
        DbUtil.daoSevice = daoSevice;
    }

    private DbUtil() {

    }

    /**
     * 获取事务管理器
     * 
     * @return
     * @throws Exception
     */
    public static PlatformTransactionManager getPlatformTransactionManager() throws Exception {
        return daoSevice.getPlatformTransactionManager();
    }

    public static String save(String tableName, Map<String, Object> data) throws Exception {
        if (tableName == null || data == null || data.isEmpty()) {
            return null;
        }
        handleNull(tableName, data);
        return daoSevice.save(tableName, data);
    }

    public static String update(String tableName, Map<String, Object> data) throws Exception {
        if (tableName == null || data == null || data.isEmpty()) {
            return null;
        }
        handleNull(tableName, data);
        return daoSevice.update(tableName, data);
    }

    public static String saveOrUpdate(String tableName, Map<String, Object> data) throws Exception {
        if (tableName == null || data == null || data.isEmpty()) {
            return null;
        }
        handleNull(tableName, data);
        Object wid = data.get("WID");
        if (StringUtils.isEmpty(wid)) {
            data.put("WID", getNewWid());
            return daoSevice.save(tableName, data);
        } else {
            return daoSevice.update(tableName, data);
        }
    }

    public static void saveOrUpdate(String tableName, List<Map<String, Object>> datas) throws Exception {
        if (tableName == null || datas == null || datas.isEmpty()) {
            return;
        }
        handleNull(tableName, datas);
        List<Map<String, Object>> saveList = new ArrayList<Map<String, Object>>();
        List<Map<String, Object>> updateList = new ArrayList<Map<String, Object>>();
        for (Map<String, Object> data : datas) {
            Object wid = data.get("WID");
            if (StringUtils.isEmpty(wid)) {
                data.put("WID", getNewWid());
                saveList.add(data);
            } else {
                updateList.add(data);
            }
        }
        if (!saveList.isEmpty()) {
            daoSevice.save(tableName, saveList);
        }
        if (!updateList.isEmpty()) {
            daoSevice.update(tableName, updateList);
        }
    }

    public static void save(String tableName, List<Map<String, Object>> datas) throws Exception {
        if (tableName == null || datas == null || datas.isEmpty()) {
            return;
        }
        handleNull(tableName, datas);
        daoSevice.save(tableName, datas);
    }

    public static void update(String tableName, List<Map<String, Object>> datas) throws Exception {
        if (tableName == null || datas == null || datas.isEmpty()) {
            return;
        }
        handleNull(tableName, datas);
        daoSevice.update(tableName, datas);
    }

    public static int deleteByWid(String tableName, String wid) throws Exception {
        return daoSevice.deleteByWid(tableName, wid);
    }

    public static int delete(String tableName, String condition, Object[] params) throws Exception {
        return daoSevice.delete(tableName, condition, params);
    }

    public static int execute(String sql, Object[] params) throws Exception {
        return daoSevice.execute(sql, params);
    }

    public static List<Map<String, Object>> query(String sql, Object[] params) throws Exception {
        return daoSevice.query(sql, params);
    }

    public static List<Map<String, Object>> query(String sql, PageRowBounds page, String sort, Object[] params) throws Exception {
        return daoSevice.query(sql, page, sort, params);
    }

    public static List<Map<String, Object>> query(String sql, PageRowBounds page, Object[] params) throws Exception {
        return daoSevice.query(sql, page, params);
    }

    public static Map<String, Object> queryRow(String sql, Object[] params) throws Exception {
        return daoSevice.queryRow(sql, params);
    }

    public static Object queryOne(String sql, Object[] params) throws Exception {
        return daoSevice.queryOne(sql, params);
    }

    public static List<Object> queryOnes(String sql, Object[] params) throws Exception {
        return daoSevice.queryOnes(sql, params);
    }

    public static int queryParseInt(String sql, Object[] params) throws Exception {
        return daoSevice.queryParseInt(sql, params);
    }

    public static List<Map<String, Object>> queryTable(String tablename, String condition, Object[] params) throws Exception {
        return daoSevice.queryTable(tablename, condition, params);
    }

    public static List<Map<String, Object>> queryTable(String tablename, String condition, Object[] params, String sort) throws Exception {
        return daoSevice.queryTable(tablename, condition, params, sort);
    }

    public static Map<String, Object> queryTableByWid(String tablename, String wid) throws Exception {
        return daoSevice.queryTableByWid(tablename, wid);
    }

    public static Map<String, Object> queryTableRow(String tablename, String condition, Object[] params) throws Exception {
        return daoSevice.queryTableRow(tablename, condition, params);
    }

    public static List<Map<String, Object>> queryTable(String tablename, PageRowBounds page, String condition, Object[] params) throws Exception {
        return daoSevice.queryTable(tablename, page, condition, params);
    }

    public static List<Map<String, Object>> queryTable(String tablename, PageRowBounds page, String ordery, String condition, Object[] params)
            throws Exception {
        return daoSevice.queryTable(tablename, page, ordery, condition, params);
    }

    public static long queryTableCount(String tablename, String condition, Object[] params) throws Exception {
        return daoSevice.queryTableCount(tablename, condition, params);
    }

    public static String getNewWid() throws Exception {
        return daoSevice.getNewWid();
    }

    public static TableMetaData getTableMeta(String tableName) throws Exception {
        return daoSevice.getTableMeta(tableName);
    }

    public static String getDatabaseProductName() throws Exception {
        return daoSevice.getDatabaseProductName();
    }

    /**
     * 获取数据库当前时间日期字符串 yyyy-MM-dd
     * 
     * @return
     * @throws Exception
     */
    public static String getCurrentDateStr() throws Exception {
        return getCurrentDateTime().toLocalDateTime().format(DATE_FORMATTER);
    }

    /**
     * 获取数据库当前时间字符串 yyyy-MM-dd HH:mm:ss
     * 
     * @return
     * @throws Exception
     */
    public static String getCurrentDateTimeStr() throws Exception {
        return getCurrentDateTime().toLocalDateTime().format(DATETIME_FORMATTER);
    }

    /**
     * 获取数据库当前时间字符串 yyyy-MM-dd HH:mm:ss SSS
     * 
     * @return
     * @throws Exception
     */
    public static String getCurrentLongDateTimeStr() throws Exception {
        return getCurrentDateTime().toLocalDateTime().format(LONG_DATETIME_FORMATTER);
    }

    /**
     * 获取当前时间
     * 
     * @return
     * @throws Exception
     */
    public static Timestamp getCurrentDateTime() throws Exception {
        String productname = getDatabaseProductName().toUpperCase();
        String sql = null;
        if ("oracle".equalsIgnoreCase(productname)) {
            sql = "SELECT SYSTIMESTAMP FROM DUAL";
        } else if ("mysql".equalsIgnoreCase(productname)) {
            sql = "SELECT NOW()";
        } else if ("h2".equalsIgnoreCase(productname)) {
            sql = "SELECT CURRENT_TIMESTAMP()";
        }
        // 如果获取不到类型，则用oracle数据库
        if (StringUtils.isEmpty(sql)) {
            sql = "SELECT SYSTIMESTAMP FROM DUAL";
        }
        return daoSevice.getJdbcTemplate().query(sql, new ResultSetExtractor<Timestamp>() {
            @Override
            public Timestamp extractData(ResultSet rs) throws SQLException, DataAccessException {
                if (rs.next()) {
                    return rs.getTimestamp(1);
                }
                return null;
            }
        });
    }

    public static void rollbackOnly() throws Exception {
        TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
    }

    /**
     * 处理空值
     * 
     * @param tableName
     * @param datas
     * @throws Exception
     */
    public static void handleNull(String tableName, List<Map<String, Object>> datas) throws Exception {
        if (datas == null || datas.isEmpty()) {
            return;
        }
        for (int i = 0, len = datas.size(); i < len; i++) {
            Map<String, Object> data = datas.get(i);
            handleNull(tableName, data);
        }
    }

    /**
     * 处理空值
     * 
     * @param tableName
     * @param datas
     * @throws Exception
     */
    public static void handleNull(String tableName, Map<String, Object> data) throws Exception {
        if (data == null || data.isEmpty()) {
            return;
        }
        Iterator<Entry<String, Object>> iter = data.entrySet().iterator();
        while (iter.hasNext()) {
            Entry<String, Object> entry = iter.next();
            String key = entry.getKey();
            Object value = entry.getValue();
            if (StringUtils.isEmpty(value)) {
                data.put(key, null);
            }
        }
    }

}
